﻿using OneSoft.DataAccess;
using OneSoft.EntityCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace BySoft.Server.Business.UserCode
{
    public partial class BsReturnMatDataAccess
    {
        internal static DataTable SelectList(string strBillID, string strFrom, string strTo, string strWorkOrderNo, string strMaterialName, int IReturnMatCount, out int icurPageIndex, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                #region SQL Server
                string strcmd = @"select A.* from V_ReturnMatDetail A WHERE 1=1 ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                if (!string.IsNullOrEmpty(strBillID))
                {
                    strcmd += " and A.BillID LIKE @strBillID";
                    dpc.AddWithValue("@strBillID", "%" + strBillID + "%");
                }

                if (!string.IsNullOrEmpty(strFrom))
                {
                    strcmd += " and A.BillDate >= @strFrom";
                    dpc.AddWithValue("@strFrom", strFrom);
                }
                if (!string.IsNullOrEmpty(strTo))
                {
                    strcmd += " and A.BillDate <= @strTo";
                    dpc.AddWithValue("@strTo", strTo);
                }

                if (!string.IsNullOrEmpty(strWorkOrderNo))
                {
                    strcmd += " and A.WorkOrderNo LIKE @WorkOrderNo";
                    dpc.AddWithValue("@WorkOrderNo", "%" + strWorkOrderNo + "%");
                }

                if (!string.IsNullOrEmpty(strMaterialName))
                {
                    strcmd += " and A.MaterialName LIKE @MaterialName";
                    dpc.AddWithValue("@MaterialName", "%" + strMaterialName + "%");
                }


                strcmd += " ORDER BY A.BillID DESC";

                return DataAccessCommon.ExecuteDataTablePagingExt(strcmd, out icurPageIndex, pageIndex, PageSize, dpc, IReturnMatCount, out m_count);
                #endregion
            }
            catch
            {
                throw;
            }
        }

        internal static DataTable SelectDialogScheduleDetailList(string strWorkOrderNo, string strScheduleID, string strProductID, string strProductName, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                #region SQL Server
                string strcmd = @"select mu.*,sd.ScheduleID,sd.PlanID,sd.ScheduleNum,od.ProductID,p.ProductName  
                                    from BS_MatUse mu
                                    left join BS_ScheduleDetail sd on sd.ScheduleDetailID=mu.ScheduleDetailID
                                    left join BS_OrderDetail od on od.WorkOrderNo=sd.WorkOrderNo
                                    left join MD_Product p on p.ProductID=od.ProductID
                                    where 1=1 and od.IsFinish<>'1' ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                if (!string.IsNullOrEmpty(strWorkOrderNo))
                {
                    strcmd += " and mu.WorkOrderNo LIKE @strWorkOrderNo";
                    dpc.AddWithValue("@strWorkOrderNo", "%" + strWorkOrderNo + "%");
                }
                if (!string.IsNullOrEmpty(strScheduleID))
                {
                    strcmd += " and sd.ScheduleID LIKE @strScheduleID";
                    dpc.AddWithValue("@strScheduleID", "%" + strScheduleID + "%");
                }
                if (!string.IsNullOrEmpty(strProductID))
                {
                    strcmd += " and od.ProductID LIKE @strProductID";
                    dpc.AddWithValue("@strProductID", "%" + strProductID + "%");
                }
                if (!string.IsNullOrEmpty(strProductName))
                {
                    strcmd += " and p.ProductName LIKE @strProductName";
                    dpc.AddWithValue("@strProductName", "%" + strProductName + "%");
                }
                strcmd += " ORDER BY mu.BillID DESC";

                return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
                #endregion
            }
            catch
            {
                throw;
            }
        }

        public static DataTable SelectMaterial(string strMaterialID, string strMaterialName, string ScheduleDetailID, string BillID, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                #region SQL Server
                string strcmd = @"
select mu.*,sd.ScheduleID,sd.PlanID,sd.ScheduleNum,od.ProductID,p.ProductName,
		mud.BillDetailID as MatUseDetailID,mud.ScheduleMatID,mud.MaterialID,mud.Nums as UseNums,mud.MMNumber,mud.Remark,
		m.MaterialName,m.Specifications,m.Unit,m.MMNumber as SingleMMNumber,bd.ChangeNum,
		c.RecNums,ISNULL(mud.Nums,0)-ISNULL(c.RecNums,0) as LeftNums,v.ThisNum as StockNums
from BS_MatUse mu
left join BS_MatUseDetail mud on mud.BillID=mu.BillID
left join BS_ScheduleDetail sd on sd.ScheduleDetailID=mu.ScheduleDetailID
left join BS_OrderDetail od on od.WorkOrderNo=sd.WorkOrderNo
left join MD_Product p on p.ProductID=od.ProductID
left join MD_Material m on m.MaterialID=mud.MaterialID
left join BS_ScheduleMat sm on sm.ScheduleMatID=mud.ScheduleMatID
left join V_MaterialStockNum v on v.ID=m.MaterialID
left join BS_BOMDetail bd on bd.BOMDetailID=sm.BOMDetailID
left join (select MatUseDetailID,SUM(ISNULL(Nums,0)) as RecNums 
            from  BS_ReturnMatDetail 
            where BillID<>@BillID
            group by MatUseDetailID) c on c.MatUseDetailID=mud.BillDetailID
where ISNULL(mud.Nums,0)-ISNULL(c.RecNums,0)>0 AND sm.ScheduleDetailID=@ScheduleDetailID
";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                dpc.AddWithValue("@ScheduleDetailID", ScheduleDetailID);
                dpc.AddWithValue("@BillID", BillID);
                if (!string.IsNullOrEmpty(strMaterialID))
                {
                    strcmd += " and mud.MaterialID LIKE @strMaterialID";
                    dpc.AddWithValue("@strMaterialID", "%" + strMaterialID + "%");
                }
                if (!string.IsNullOrEmpty(strMaterialName))
                {
                    strcmd += " and m.MaterialName LIKE @strMaterialName";
                    dpc.AddWithValue("@strMaterialName", "%" + strMaterialName + "%");
                }
                strcmd += " ORDER BY mu.BillID DESC";

                return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
                #endregion
            }
            catch
            {
                throw;
            }
        }

        internal static DataTable GetDataByID(string id)
        {

            try
            {
                #region SQL Server
                string strcmd = @"select rm.*,rmd.BillDetailID,rmd.MatUseDetailID,
		rmd.MaterialID,rmd.Nums,rmd.MMNumber,rmd.Remark,
		m.MaterialName,m.Specifications,m.Unit,m.MMNumber as SingleMMNumber,
		s.ThisNum as StockNums,bd.ChangeNum,sd.ScheduleID,mud.Nums  as UseNums,
		od.ProductID,p.ProductName,sd.ScheduleNum,
        c.RecNums,ISNULL(mud.Nums,0)-ISNULL(c.RecNums,0) as LeftNums
from BS_ReturnMat rm
left join BS_ReturnMatDetail rmd on rmd.BillID=rm.BillID
left join MD_Material m on m.MaterialID=rmd.MaterialID
left join V_MaterialStockNum s on s.ID=m.MaterialID
left join BS_MatUseDetail mud on mud.BillDetailID=rmd.MatUseDetailID
left join BS_ScheduleMat sm on sm.ScheduleMatID=mud.ScheduleMatID
left join BS_BOMDetail bd on bd.BOMDetailID=sm.BOMDetailID
left join BS_ScheduleDetail sd on sd.ScheduleDetailID=sm.ScheduleDetailID
left join BS_OrderDetail od on od.WorkOrderNo=sd.WorkOrderNo
left join MD_Product p on p.ProductID=od.ProductID
left join (select MatUseDetailID,SUM(ISNULL(Nums,0)) as RecNums 
            from  BS_ReturnMatDetail 
            where BillID<>@strBillID
            group by MatUseDetailID) c on c.MatUseDetailID=mud.BillDetailID
WHERE rm.BillID=@strBillID ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@strBillID", id);
                return DataAccessCommon.Select(strcmd, dpc).Tables[0];
                #endregion
            }
            catch
            {
                throw;
            }
        }
    }
}
